package com.qingfeng.screen.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.qingfeng.base.controller.BaseController;
import com.qingfeng.base.entity.QueryRequest;
import com.qingfeng.framework.exception.BizException;
import com.qingfeng.screen.entity.VisualDb;
import com.qingfeng.screen.service.IVisualDbService;
import com.qingfeng.utils.*;
import com.qingfeng.utils.sqlutil.MysqlUtil;
import com.qingfeng.utils.sqlutil.OracleUtil;
import com.qingfeng.utils.sqlutil.PostgreSQLUtils;
import com.qingfeng.utils.sqlutil.SqlserverUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;
import javax.validation.constraints.NotBlank;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * @ProjectName VisualMapController
 * @author Administrator
 * @version 1.0.0
 * @Description TODO
 * @createTime 2021/4/17 0017 14:58
 */
@Slf4j
@Validated
@RestController
@RequestMapping("db")
public class VisualDbController extends BaseController {

    @Autowired
    private IVisualDbService visualDbService;

    /**
     * @title findListPage
     * @description 查询数据分页列表
     * @author Administrator
     * @updateTime 2021/4/17 0017 14:51
     */
    @GetMapping("/list")
    public MyResponse findListPage(QueryRequest queryRequest, VisualDb visualDb) {
        String userParams = SecurityContextHolder.getContext().getAuthentication().getName();
        Map<String, Object> dataTable = MyUtil.getDataTable(visualDbService.findListPage(visualDb, queryRequest));
        return new MyResponse().data(dataTable);
    }

    /**
     * @title detail
     * @description 查询详情信息
     * @author Administrator
     * @updateTime 2021/4/17 0017 14:51
     */
    @GetMapping("/detail")
    public MyResponse detail(VisualDb visualDb) {
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("id",visualDb.getId());
        VisualDb detail = visualDbService.getOne(queryWrapper);
        return new MyResponse().data(detail);
    }

    /**
     * @title findList
     * @description 查询信息列表
     * @author Administrator
     * @updateTime 2021/4/17 0017 14:51
     */
    @GetMapping("/findList")
    public MyResponse findList(VisualDb visualDb) {
        String userParams = SecurityContextHolder.getContext().getAuthentication().getName();
        List<VisualDb> mapList = visualDbService.findList(visualDb);
        return new MyResponse().data(mapList);
    }



    /**
     * @title save
     * @description 保存数据
     * @author Administrator
     * @updateTime 2021/4/17 0017 14:52
     */
    @PostMapping("/save")
    public void save(@Valid @RequestBody VisualDb visualDb) throws BizException {
        try {
            this.visualDbService.saveVisualDb(visualDb);
        } catch (Exception e) {
            String message = "新增信息失败";
            log.error(message, e);
            throw new BizException(message);
        }
    }

    /**
     * @title update
     * @description 更新数据
     * @author Administrator
     * @updateTime 2021/4/17 0017 14:52
     */
    @PostMapping("/update")
    public void update(@Valid @RequestBody VisualDb visualDb) throws BizException {
        try {
            this.visualDbService.updateVisualDb(visualDb);
        } catch (Exception e) {
            String message = "修改信息失败";
            log.error(message, e);
            throw new BizException(message);
        }
    }

    /**
     * @title delete
     * @description 删除数据
     * @author Administrator
     * @updateTime 2021/4/17 0017 14:52
     */
    @DeleteMapping("/{ids}")
    public void delete(@NotBlank(message = "{required}") @PathVariable String ids) throws BizException {
        try {
            String[] del_ids = ids.split(StringPool.COMMA);
            this.visualDbService.removeByIds(Arrays.asList(del_ids));
        } catch (Exception e) {
            String message = "删除失败";
            log.error(message, e);
            throw new BizException(message);
        }
    }



    @PostMapping("/dynamic-query")
    public MyResponse dynamicQuery(@RequestBody String param) throws BizException, SQLException {
        String jsonParam = AESUtils.decrypt(param);
        PageData pd = JsonToMap.parseJSON2Pd(jsonParam);

        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("id",pd.get("id").toString());
        VisualDb detail = visualDbService.getOne(queryWrapper);

        String url = detail.getUrl();
        String username = detail.getUsername();
        String password = detail.getPassword();
        String driverClass = detail.getDriverClass();
        List<PageData> list = new ArrayList<PageData>();
        String sql = pd.get("sql").toString();
        if(driverClass.contains("mysql")){//mysql数据源
            MysqlUtil mysqlUtil = new MysqlUtil(url,username,password,driverClass);
            ResultSet resultSet = mysqlUtil.select(sql);
            list = convertList(resultSet);
        }else if(driverClass.contains("oracle")){//oracle数据源
            OracleUtil oracleUtil = new OracleUtil(url,username,password);
            ResultSet resultSet = oracleUtil.select(sql);
            list = convertList(resultSet);
        }else if(driverClass.contains("sqlserver")){//sqlserver数据源
            SqlserverUtil sqlserverUtil = new SqlserverUtil(url,username,password);
            ResultSet resultSet = sqlserverUtil.select(sql);
            list = convertList(resultSet);
        }else if(driverClass.contains("postgresql")){//postgresql数据源
            PostgreSQLUtils postgreSQLUtils = new PostgreSQLUtils(url,username,password);
            ResultSet resultSet = postgreSQLUtils.select(sql);
            list = convertList(resultSet);
        }
       return new MyResponse().data(list);
    }


    private List convertList(ResultSet rs) throws SQLException {
        List list = new ArrayList();
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount();
        while (rs.next()) {
            PageData rowData = new PageData();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }

    @PostMapping("/db-test")
    public MyResponse dbTest(@RequestBody String param) throws BizException {
        boolean bol = true;
        System.out.println("-------------------------------------------");
        System.out.println(param);
        String jsonParam = AESUtils.decrypt(param);
        System.out.println(jsonParam);
        PageData pd = JsonToMap.parseJSON2Pd(jsonParam);
        System.out.println(pd);
        String url = pd.get("url").toString();
        String username = pd.get("username").toString();
        String password = pd.get("password").toString();
        String dbDriver = pd.get("driverClass").toString();
        if(pd.get("driverClass").toString().contains("mysql")){//mysql数据源
            MysqlUtil mysqlUtil = new MysqlUtil(url,username,password,dbDriver);
            bol = mysqlUtil.getConnection();
            System.out.println(bol);
        }else if(pd.get("driverClass").toString().contains("oracle")){//oracle数据源
            OracleUtil oracleUtil = new OracleUtil(url,username,password);
            bol = oracleUtil.getConnection();
        }else if(pd.get("driverClass").toString().contains("sqlserver")){//sqlserver数据源
            SqlserverUtil sqlserverUtil = new SqlserverUtil(url,username,password);
            bol = sqlserverUtil.getConnection();
        }else if(pd.get("driverClass").toString().contains("postgresql")){//postgresql数据源
            PostgreSQLUtils postgreSQLUtils = new PostgreSQLUtils(url,username,password);
            bol = postgreSQLUtils.getConnection();
        }
        Json json = new Json();
        json.setSuccess(bol);
        return new MyResponse().data(json);
    }


}
